import pymysql
import pandas as pd

# 打开数据库连接
db = pymysql.connect(
    host="localhost",
    user="root",
    password="hui123456",
    db='dbtest'
)

# 使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()

sql1 = """
     	SELECT 
            shop,
            product_BianMa, 
            SUM(Product_num), 
            SUM(Product_price)

        FROM niandu_day 
        WHERE month(day) = 9 
            AND (
                order_zt = "完成" OR 
                order_zt = "等待出库" OR 
                order_zt = "等待确认收货" OR 
                order_zt = "已签收" OR 
                order_zt = "待配货" OR 
                order_zt = "待发货" OR 
                order_zt = "交易成功" OR 
                order_zt = "已完成" OR 
                order_zt = "卖家已发货，等待买家确认" OR 
                order_zt = "买家已付款，等待卖家发货" OR 
                order_zt = "已发货" OR 
                order_zt = "调度中" OR 
                order_zt = "已收货" OR 
                order_zt = "已发货未签收" OR 
                order_zt = "已发货，待签收" OR 
                order_zt = "(锁定)等待确认收货" OR 
                order_zt = "已发货，待收货" OR 
                order_zt = "部分发货" OR 
                order_zt = "买家已付款,等待卖家发货" OR 
                order_zt = "卖家已发货" OR 
                order_zt = "买家已付款" OR 
                order_zt = "发货即将超时" OR 
                order_zt = "卖家部分发货" OR 
                order_zt = "待买家收货" OR 
                order_zt = "待卖家发货" OR 
                order_zt = "部分发货中"
            )
            AND order_sh != "退款成功"  
            AND order_sh != "售后完成"  
            AND order_sh != "退款完成"  
            AND order_sh != "退货退款完成 "
        GROUP BY shop, product_BianMa

     """

cursor.execute(sql1)
result1 = cursor.fetchall()
xs_xs = pd.DataFrame(list(result1))
# xs_xs.columns = ['店铺', '编码', '商品数量', '应付', '平台优惠']
xs_xs.columns = ['店铺', '编码', '商品数量', '应付']
xs_xs.to_excel(r'G:\工作\SKU利润测算\2025年\9月\9月利润测算明细_线上.xlsx')

sql11 = """
     	SELECT 
     	        * 
        FROM doudian_day 
        WHERE month(day) = 9 
            AND dianzhuguo = "电煮锅赠品" 
            AND (
                order_zt = "已签收" OR 
                order_zt = "待发货" OR 
                order_zt = "已完成" OR 
                order_zt = "已发货" OR 
                order_zt = "已支付"
            )
            AND order_sh != "退款成功"  
            AND order_sh != "售后完成"  
            AND order_sh != "退款完成"  
            AND order_sh != "已全额退款"

     """
cursor.execute(sql11)
result11 = cursor.fetchall()
dg = pd.DataFrame(list(result11))
# dg.columns=['店铺','编码','商品数量','应付','平台优惠']
dg.to_excel(r'G:\工作\SKU利润测算\2025年\9月\9月锅.xlsx')

sql2 = """
        SELECT 
                product_BianMa,
                SUM(Product_num),
                SUM(Product_price),
                SUM(yh_pingtai),
                SUM(yh_daren) 
        FROM doudian_day  
        WHERE month(day) = 9 
            AND (
                order_zt = "已签收" OR 
                order_zt = "待发货" OR 
                order_zt = "已完成" OR 
                order_zt = "已发货" OR 
                order_zt = "已支付"
            )
            AND order_sh != "退款成功"  
            AND order_sh != "售后完成"  
            AND order_sh != "退款完成"  
            AND order_sh != "已全额退款"
        GROUP BY product_BianMa


    """

sql22 = """
        SELECT 
            product_BianMa,
            Product_dj,
            SUM(Product_num),
            SUM(Product_price),
            SUM(yh_pingtai) 
        FROM doudian_day  
        WHERE month(day) = 9 
            AND (
                order_zt = "已签收" OR 
                order_zt = "待发货" OR 
                order_zt = "已完成" OR 
                order_zt = "已发货" OR 
                order_zt = "已支付"
            )
            AND order_sh != "退款成功"  
            AND order_sh != "售后完成"  
            AND order_sh != "退款完成"  
            AND order_sh != "已全额退款"
        GROUP BY product_BianMa, Product_dj


    """

cursor.execute(sql2)
result2 = cursor.fetchall()

jg = pd.DataFrame(list(result2))
jg.to_excel(r'G:\工作\SKU利润测算\2025年\9月\9月抖音编码价格.xlsx')

cursor.execute(sql22)
result22 = cursor.fetchall()

dj = pd.DataFrame(list(result22))
dj.to_excel(r'G:\工作\SKU利润测算\2025年\9月\9月抖音编码单价.xlsx')
